package cn.edu.cug.cs.gtl.utils.postgresql;

import cn.edu.cug.cs.gtl.protos.DataGroup;
import cn.edu.cug.cs.gtl.protos.DataItem;
import cn.edu.cug.cs.gtl.protos.DataKey;
import cn.edu.cug.cs.gtl.utils.DataCatalogManager;
import cn.edu.cug.cs.gtl.utils.DataGroupManager;
import cn.edu.cug.cs.gtl.utils.DataItemManager;
import cn.edu.cug.cs.gtl.utils.impl.DataItemManagerImpl;
import com.google.protobuf.InvalidProtocolBufferException;
import org.apache.hadoop.yarn.webapp.hamlet.Hamlet;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DataGroupManager4Postgresql implements DataGroupManager {
    @Override
    public boolean insertDataGroup(DataGroup dataGroup) {
        String group_name = dataGroup.getName();
        boolean ret = DataCatalogManager4Postgresql.is_name_exist(group_name);
        if (ret)
            return false;

        //将dataGroup对象插入到data_group表中
        PostgresqlUtil util = new PostgresqlUtil();
        String alias = dataGroup.getAlias();
        String description = dataGroup.getDescription();

        String insertSql = "INSERT INTO public.data_group (name,alias,description) VALUES (?,?,?)";
        Connection conn = util.getConnection();
        try {
            PreparedStatement statement = conn.prepareStatement(insertSql);
            statement.setString(1, group_name);
            statement.setString(2, alias);
            statement.setString(3, description);
            if (statement.executeUpdate() == 1)
                return true;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            util.closeConnection(conn);
        }
        return false;
    }

    @Override
    public DataGroup removeDataGroup(String dataGroupName) {
        PostgresqlUtil util = new PostgresqlUtil();
        Connection conn = util.getConnection();
        try {
            //在data_group表中查询名称为dataGroupName的记录的信息，用于构造DataGroup对象返回
            String querySql = "SELECT id,alias,description,order_id,cid FROM public.data_group WHERE name = ?";
            PreparedStatement statement = conn.prepareStatement(querySql);
            statement.setString(1, dataGroupName);
            ResultSet resultSet = statement.executeQuery();
            if (!resultSet.next())  //被删除的记录不存在
                return null;
            int group_id = resultSet.getInt(1);
            String group_alias = resultSet.getString(2);
            String group_description = resultSet.getString(3);
            int group_order_id = resultSet.getInt(4);
            int group_cid = resultSet.getInt(5);

            //更行在data_group表中order_id 大于 group_order_id 的记录的order_id;
            String updateSql = "UPDATE public.data_group SET order_id = order_id -1 WHERE cid = ? AND order_id > ?";
            statement = conn.prepareStatement(updateSql);
            statement.setInt(1, group_cid);
            statement.setInt(2, group_order_id);
            statement.executeUpdate();

            //在data_group表中删除这条记录
            String deleteSql = "DELETE FROM public.data_group WHERE name = ?";
            statement = conn.prepareStatement(deleteSql);
            statement.setString(1, dataGroupName);
            statement.executeUpdate();
            return DataGroup.newBuilder().setName(dataGroupName).setAlias(group_alias)
                    .setDescription(group_description).addAllDataItemName(DataCatalogManager4Postgresql.getDataItemNameChildrenFromId(group_id)).build();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            util.closeConnection(conn);
        }
    }

    @Override
    public boolean updateDataGroup(DataGroup dataGroup) {
        String name = dataGroup.getName();
        String new_alias = dataGroup.getAlias();
        String new_description = dataGroup.getDescription();
        String updateSql = "UPDATE public.data_group SET alias = ? , description = ? WHERE name = ?";
        PostgresqlUtil util = new PostgresqlUtil();
        Connection conn = util.getConnection();
        try {
            PreparedStatement statement = conn.prepareStatement(updateSql);
            statement.setString(1, new_alias);
            statement.setString(2, new_description);
            statement.setString(3, name);
            int ret = statement.executeUpdate();
            if (ret == 1)
                return true;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return false;
    }

    @Override
    public DataGroup getDataGroup(String dataGroupName) {
        PostgresqlUtil util = new PostgresqlUtil();
        Connection conn = util.getConnection();
        try {
            String querySql = "SELECT id,alias,description FROM public.data_group WHERE name = ?";
            PreparedStatement statement = conn.prepareStatement(querySql);
            statement.setString(1, dataGroupName);
            ResultSet resultSet = statement.executeQuery();
            if (!resultSet.next())
                return null;
            int id = resultSet.getInt(1);
            String group_alias = resultSet.getString(2);
            String group_description = resultSet.getString(3);

            return DataGroup.newBuilder().setName(dataGroupName).setAlias(group_alias)
                    .setDescription(group_description).addAllDataItemName(DataCatalogManager4Postgresql.getDataItemNameChildrenFromId(id)).build();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            util.closeConnection(conn);
        }
    }

    @Override
    public List<DataItem> getDataItemsInGroup(String dataGroupName) {
        PostgresqlUtil util = new PostgresqlUtil();
        Connection conn = util.getConnection();
        ArrayList<DataItem> arrayList = new ArrayList<>();
        int group_id = DataCatalogManager4Postgresql.getGroupIdFromName(dataGroupName);
        if (group_id == -1)    //指定数据组不存在
            return null;

        List<String> itemNameList = DataCatalogManager4Postgresql.getDataItemNameChildrenFromId(group_id);
        try {
            for (String str : itemNameList)
                arrayList.add(new DataItemManagerImpl().getDataItem(str));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return arrayList;
    }

    @Override
    public boolean addDataItemToGroup(String dataGroupName, String dataItemName) {
        int parent_id = DataCatalogManager4Postgresql.getGroupIdFromName(dataGroupName);

        PostgresqlUtil util = new PostgresqlUtil();
        Connection conn = util.getConnection();
        try {
            //获取group对象包含的数据项数量
            String querySql = "SELECT COUNT(*) FROM public.data_item WHERE gid = ?";
            PreparedStatement statement = conn.prepareStatement(querySql);
            statement.setInt(1, parent_id);
            ResultSet resultSet = statement.executeQuery();
            resultSet.next();
            int item_order_id = resultSet.getInt(1) + 1;

            //在data_item表中更新dataItem对象的gid和order_id
            String updateSql = "UPDATE public.data_item SET order_id = ?,gid = ? WHERE name = ?";
            statement = conn.prepareStatement(updateSql);
            statement.setInt(1, item_order_id);
            statement.setInt(2, parent_id);
            statement.setString(3, dataItemName);
            int retU = statement.executeUpdate();
            if (retU == 1)
                return true;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            util.closeConnection(conn);
        }
        return false;
    }

    @Override
    public boolean removeDataItemFromGroup(String dataGroupName, String dataItemName) {
        int parent_id = DataCatalogManager4Postgresql.getGroupIdFromName(dataGroupName);
        if (parent_id == -1)
            return false;

        PostgresqlUtil util = new PostgresqlUtil();
        Connection conn = util.getConnection();
        try {
            String querySql = "SELECT id,order_id FROM public.data_item WHERE gid = ? AND name = ?";
            PreparedStatement statement = conn.prepareStatement(querySql);
            statement.setInt(1, parent_id);
            statement.setString(2, dataItemName);
            ResultSet resultSet = statement.executeQuery();
            if (!resultSet.next())
                return false;
            int item_id = resultSet.getInt(1);
            int item_order_id = resultSet.getInt(2);

            //更新data_item表中order_id值 大于 被删除的DataItem对象的order_id
            String updateSql = "UPDATE public.data_item SET order_id = order_id -1 WHERE order_id > ?";
            statement = conn.prepareStatement(updateSql);
            statement.setInt(1, item_order_id);
            statement.executeUpdate();

            //删除data_item表中的该记录
            String deleteSql = "DELETE FROM public.data_item WHERE id = ?";
            statement = conn.prepareStatement(deleteSql);
            statement.setInt(1, item_id);
            int ret = statement.executeUpdate();
            if (ret == 1)
                return true;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return false;
    }
}
